/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v5.2.3                     */
/* Target DBMS:           MS SQL Server 2005                              */
/* Project file:          DMS_DB_Diagram_Release3.dez                     */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2010-01-19 00:58                                */
/* Model version:         Version 2010-01-19                              */
/* ---------------------------------------------------------------------- */
CREATE DATABASE DefectManagementSystem
GO
USE DefectManagementSystem

/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "dms_Severity"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_Severity] (
    [Severity_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [SeverityName] VARCHAR(30) NOT NULL,
    CONSTRAINT [PK_dms_Severity] PRIMARY KEY ([Severity_Id]),
    CONSTRAINT [TUC_dms_Severity_1] UNIQUE ([SeverityName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_Priority"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_Priority] (
    [Priority_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [PriorityName] VARCHAR(30) NOT NULL,
    CONSTRAINT [PK_dms_Priority] PRIMARY KEY ([Priority_Id]),
    CONSTRAINT [TUC_dms_Priority_1] UNIQUE ([PriorityName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_Comment"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_Comment] (
    [Comment_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [Defect_Id] INTEGER NOT NULL,
    [User_Id] INTEGER NOT NULL,
    [CreatedDate] VARCHAR(40) NOT NULL,
    [Content] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_dms_Comment] PRIMARY KEY ([Comment_Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_Origin"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_Origin] (
    [Origin_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [OriginName] VARCHAR(30) NOT NULL,
    CONSTRAINT [PK_dms_Origin] PRIMARY KEY ([Origin_Id]),
    CONSTRAINT [TUC_dms_Origin_1] UNIQUE ([OriginName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Project"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Project] (
    [Project_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [ProjectName] VARCHAR(40) NOT NULL,
    [Description] VARCHAR(max),
    [CreatedDate] DATETIME NOT NULL,
    [ProjectStatus_Id] INTEGER NOT NULL,
    CONSTRAINT [PK_Project] PRIMARY KEY ([Project_Id]),
    CONSTRAINT [TUC_Project_1] UNIQUE ([ProjectName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Users"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Users] (
    [User_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [UserName] VARCHAR(25) NOT NULL,
    [Password] VARCHAR(25) NOT NULL,
    [FullName] VARCHAR(50) NOT NULL,
    [Email] VARCHAR(50) NOT NULL,
    [Join_Date] DATETIME NOT NULL,
    [Role_Id] INTEGER NOT NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([User_Id]),
    CONSTRAINT [TUC_Users_1] UNIQUE ([UserName], [Email])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_AttachFile"                                             */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_AttachFile] (
    [AttachFile_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [Defect_Id] INTEGER NOT NULL,
    [FileName] VARCHAR(40) NOT NULL,
    [Path] VARCHAR(100) NOT NULL,
    [Description] VARCHAR(max),
    [CreatedDate] DATETIME NOT NULL,
    CONSTRAINT [PK_dms_AttachFile] PRIMARY KEY ([AttachFile_Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_Defect"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_Defect] (
    [Defect_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [Project_Id] INTEGER NOT NULL,
    [Created_by] INTEGER NOT NULL,
    [Defected_by] INTEGER NOT NULL,
    [Assigned_to] INTEGER,
    [Severity_Id] INTEGER NOT NULL,
    [Priority_Id] INTEGER NOT NULL,
    [DefectType_Id] INTEGER NOT NULL,
    [CreatedDate] DATETIME NOT NULL,
    [Deadline] DATETIME,
    [QCActivity_Id] INTEGER NOT NULL,
    [Origin_Id] INTEGER NOT NULL,
    [ProductType_Id] INTEGER NOT NULL,
    [TestCase_Id] VARCHAR(40),
    [Product_Id] INTEGER,
    [FixedDate] DATETIME,
    [ClosedDate] DATETIME,
    [DefectStatus_Id] INTEGER NOT NULL,
    [Title] NVARCHAR(100) NOT NULL,
    [Description] NVARCHAR(Max) NOT NULL,
    [CauseAnalysis] NVARCHAR(Max),
    [CorrectiveAction] NVARCHAR(Max),
    CONSTRAINT [PK_dms_Defect] PRIMARY KEY ([Defect_Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_DefectStatus"                                           */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_DefectStatus] (
    [DefectStatus_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [DefectStatusName] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_dms_DefectStatus] PRIMARY KEY ([DefectStatus_Id]),
    CONSTRAINT [TUC_dms_DefectStatus_1] UNIQUE ([DefectStatusName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_ProjectStatus"                                          */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_ProjectStatus] (
    [ProjectStatus_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [ProjectStatusName] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_dms_ProjectStatus] PRIMARY KEY ([ProjectStatus_Id]),
    CONSTRAINT [TUC_dms_ProjectStatus_1] UNIQUE ([ProjectStatusName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_QCActivity"                                             */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_QCActivity] (
    [QCActivity_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [QCActivityName] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_dms_QCActivity] PRIMARY KEY ([QCActivity_Id]),
    CONSTRAINT [TUC_dms_QCActivity_1] UNIQUE ([QCActivityName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "ProductType"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE [ProductType] (
    [ProductType_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [ProductTypeName] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_ProductType] PRIMARY KEY ([ProductType_Id]),
    CONSTRAINT [TUC_ProductType_1] UNIQUE ([ProductTypeName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_History"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_History] (
    [History_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [Defect_Id] INTEGER NOT NULL,
    [Pre_Status] VARCHAR(40) NOT NULL,
    [New_Status] VARCHAR(40) NOT NULL,
    [CreatedBy] INTEGER NOT NULL,
    [CreatedDate] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_dms_History] PRIMARY KEY ([History_Id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Product"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Product] (
    [Product_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [ProductName] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_Product] PRIMARY KEY ([Product_Id]),
    CONSTRAINT [TUC_Product_1] UNIQUE ([ProductName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "dms_DefectType"                                             */
/* ---------------------------------------------------------------------- */

CREATE TABLE [dms_DefectType] (
    [DefectType_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [DefectTypeName] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_dms_DefectType] PRIMARY KEY ([DefectType_Id]),
    CONSTRAINT [TUC_dms_DefectType_1] UNIQUE ([DefectTypeName])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Roles"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Roles] (
    [Role_Id] INTEGER IDENTITY(1,1) NOT NULL,
    [RoleName] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_Roles] PRIMARY KEY ([Role_Id]),
    CONSTRAINT [TUC_Roles_1] UNIQUE ([RoleName])
)
GO

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [dms_Comment] ADD CONSTRAINT [Users_dms_Comment] 
    FOREIGN KEY ([User_Id]) REFERENCES [Users] ([User_Id])
GO

ALTER TABLE [dms_Comment] ADD CONSTRAINT [dms_Defect_dms_Comment] 
    FOREIGN KEY ([Defect_Id]) REFERENCES [dms_Defect] ([Defect_Id])
GO

ALTER TABLE [Project] ADD CONSTRAINT [dms_ProjectStatus_Project] 
    FOREIGN KEY ([ProjectStatus_Id]) REFERENCES [dms_ProjectStatus] ([ProjectStatus_Id])
GO

ALTER TABLE [Users] ADD CONSTRAINT [Roles_Users] 
    FOREIGN KEY ([Role_Id]) REFERENCES [Roles] ([Role_Id])
GO

ALTER TABLE [dms_AttachFile] ADD CONSTRAINT [dms_Defect_dms_AttachFile] 
    FOREIGN KEY ([Defect_Id]) REFERENCES [dms_Defect] ([Defect_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [Project_dms_Defect] 
    FOREIGN KEY ([Project_Id]) REFERENCES [Project] ([Project_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [dms_Origin_dms_Defect] 
    FOREIGN KEY ([Origin_Id]) REFERENCES [dms_Origin] ([Origin_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [dms_DefectStatus_dms_Defect] 
    FOREIGN KEY ([DefectStatus_Id]) REFERENCES [dms_DefectStatus] ([DefectStatus_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [dms_Severity_dms_Defect] 
    FOREIGN KEY ([Severity_Id]) REFERENCES [dms_Severity] ([Severity_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [dms_Priority_dms_Defect] 
    FOREIGN KEY ([Priority_Id]) REFERENCES [dms_Priority] ([Priority_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [ProductType_dms_Defect] 
    FOREIGN KEY ([ProductType_Id]) REFERENCES [ProductType] ([ProductType_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [Product_dms_Defect] 
    FOREIGN KEY ([Product_Id]) REFERENCES [Product] ([Product_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [dms_DefectType_dms_Defect] 
    FOREIGN KEY ([DefectType_Id]) REFERENCES [dms_DefectType] ([DefectType_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [dms_QCActivity_dms_Defect] 
    FOREIGN KEY ([QCActivity_Id]) REFERENCES [dms_QCActivity] ([QCActivity_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [Users_dms_Defect] 
    FOREIGN KEY ([Created_by]) REFERENCES [Users] ([User_Id])
GO

ALTER TABLE [dms_Defect] ADD CONSTRAINT [Users_dms_Defect2] 
    FOREIGN KEY ([Assigned_to]) REFERENCES [Users] ([User_Id])
GO

ALTER TABLE [dms_History] ADD CONSTRAINT [dms_Defect_dms_History] 
    FOREIGN KEY ([Defect_Id]) REFERENCES [dms_Defect] ([Defect_Id])
GO
